#~~~~~~Define_Browser~~~~~~#
def getDriver():
    from source import executable_path
    from source import firefox_profile
    import importlib
    source = importlib.__import__('source')
    importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    from source import executable_path
    from source import firefox_profile
    from selenium import webdriver
    from selenium.webdriver.firefox.options import Options
    from selenium.webdriver.firefox.firefox_profile import FirefoxProfile
    options = Options()
    options.add_argument('--headless')
    driver = webdriver.Firefox(
            #插入驱动器路径
            executable_path = executable_path,
            #使浏览器在运行时不在当前使用界面显示，headless
            options = options,
            #运用本地数据来模拟非机器人环境，当本地火狐浏览器已登陆是，使用此路径将在访问该网站时自动登陆
            firefox_profile = FirefoxProfile(firefox_profile)
            )
    print("浏览器已准备！")
    return driver;



#~~~~~~Define_Basic_Info~~~~~~#
class nameBasicInfo:
    def __init__(self, link, pub_time):
        self.link = link
        self.pub_time = pub_time



#~~~~~~Define_getBasicInfo~~~~~~#
def getBasicInfo(url, driver):
    import source
    import importlib 
    source = importlib.__import__('source')
    importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    from source import sql_driver
    from source import server
    from source import database
    from source import trusted_connection
    from source import table
    from source import source
    from source import targets_parent_selector
    from source import targets_set_tag_name
    from source import targets_tag_name
    import time
    import pyodbc
    from source import uid
    from source import pwd
    from source import targets_set_locate_method
    from source import targets_set_class_name
    from source import link_title_relation
    from source import link_tag_name
    from source import title_tag_name
    from source import title_attribute_name
    from source import link_attribute_name
    from source import title_locate_method
    from source import targets_location_method
    from source import targets_class_name
    from source import link_elem_x_path_level
    from source import port 
    from source import keep_pub_time
    from source import pub_time_elem_locate_method
    from source import pub_time_class
    from source import pub_time_tag
    from source import pub_time_locate_method
    from source import pub_time_attribute_name
    from source import limit
    import datetime
    basic_info_list = []
    temp_list = []
    try:
        #~~~~~~Connect_to_Sql~~~~~~#
        connect_input = "driver=" + sql_driver + ";"
        connect_input = connect_input + "Server=" + server + ";"
        connect_input = connect_input + "Database=" + database + ";"
        connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
        if port != "":
            connect_input = connect_input + "PORT=" + port + ";"
        if uid != '' and pwd != '':
            connect_input = connect_input + "UID=" + uid + ";"
            connect_input = connect_input + "PWD=" + pwd
        conn = pyodbc.connect(connect_input)
        cursor = conn.cursor()
        #将已导入的文章过滤掉
        #~~~~~~Make_Existing_Title_List_Filter~~~~~~#
        existing_title_list = []
        #近九十天以内的过滤掉
        start_time = datetime.datetime.now()-datetime.timedelta(days=90)
        start_time = start_time.strftime("%Y-%m-%d %H:%M:%S")
        start_time = str(start_time)
        order_0 = "select Title from " + table + " where Source = \'" + source + "\' and CreateDate > \'" + start_time + "\'"
        cursor.execute(order_0)
        existing_title_list = list(cursor.fetchall())
        print("将对比", len(existing_title_list), "条已爬取新闻 which scrapped after", str(start_time))
    except Exception as e:
        print(e)
        pass
    #~~~~~~Start_Finding_Basic_Info~~~~~~#
    #打开网页后休眠10秒以至于网页完全加载
    driver.get(url)
    time.sleep(10)
    #寻找link所在的家长级元素的家长级元素
    #~~~~~~Locate_Target_Set~~~~~~#
    while True:
        try:
            targets_parent = driver.find_element_by_css_selector(targets_parent_selector)
            break   
        except Exception as e:
            print(e)
            time.sleep(5)
            driver.save_screenshot('current_page.png')
            continue                
    #寻找link所在的家长级元素（找两次家长级元素以方便driver寻找元素，driver在find过程中如果使用的寻找方法过于general，可能会找不到该元素）
    if targets_set_locate_method == "tag":
        targets = targets_parent.find_elements_by_tag_name(targets_set_tag_name)
        for target in targets:
            temp_list.insert(len(temp_list),target)
    elif targets_set_locate_method == "class":
        targets = targets_parent.find_elements_by_tag_name("*")
        for target in targets:
            if target.get_attribute("class") == targets_set_class_name:
                #找到target
                temp_list.insert(len(temp_list),target)
    #找到link以及title
    #~~~~~~Locate_Link_and_Title~~~~~~#
    for elem in temp_list:
        #~~~~~~Link_Title_Under_Same_Element~~~~~~#
        if link_title_relation == "link_title_under_target_same":
            #~~~~~~Find_Target_by_Tag~~~~~~#
            if targets_location_method == "tag":
                target_siblings = elem.find_elements_by_tag_name("*")
                for target_sibling in target_siblings:
                    if targets_tag_name == target_sibling.tag_name:
                        target = target_sibling
                        break
                #找到link
                #~~~~~~Find_Link~~~~~~#
                link = target.get_attribute(link_attribute_name)
                #找到title
                #~~~~~~Find_Title~~~~~~#
                if title_locate_method == "attribute":
                    title = target.get_attribute(title_attribute_name)
                elif title_locate_method == "text":
                    title = target.text
                para_numb = 0
                #将title对比之前已经爬取的文章，防止重复爬取
                #~~~~~~Filter_Existing_Titles~~~~~~#
                for existing_title in existing_title_list:
                    if title.replace("\'", "“").replace("\"", "“") in str(existing_title):
                        para_numb = 1
                        break
                #~~~~~~Find_Pub_Time_(Optional)~~~~~~#
                if keep_pub_time == "true":
                    ###temp_parent = 
                    if pub_time_elem_locate_method == "class":
                        temp_group = elem.find_elements_by_tag_name("*")
                        for temp_elem in temp_group:
                            if temp_elem.get_attribute("class") == pub_time_class:
                                pub_time_elem = temp_elem
                                if pub_time_locate_method == "text":
                                    pub_time = pub_time_elem.text
                elif keep_pub_time == "false":
                    pub_time = "None"
                #将target放入list
                #~~~~~~Insert_Into_List_Finally~~~~~~#
                if para_numb == 0:
                    basic_info = nameBasicInfo(link=link, pub_time=pub_time)
                    basic_info_list.insert(len(basic_info_list), basic_info)
                elif para_numb == 1:
                    pass
            #以下为通过class的方式锁定目标target，形式与此函数以上内容相似，在此不赘述
            #~~~~~~Find_Target_by_Class~~~~~~#
            elif targets_location_method == "class":
                temp_group = elem.find_elements_by_tag_name("*")
                for temp_elem in temp_group:
                    if temp_elem.get_attribute("class") == targets_class_name:
                        target = temp_elem
                #~~~~~~Find_Link~~~~~~#
                link = target.get_attribute(link_attribute_name)
                #~~~~~~Find_Title~~~~~~#
                if title_locate_method == "text":
                    title = target.text
                elif title_locate_method == "attribute":
                    title = target.get_attribute(title_attribute_name)
                #~~~~~~Filter_Existing_Title~~~~~~#
                para_numb = 0
                for existing_title in existing_title_list:
                    if title.replace("\'", "“").replace("\"", "“") in str(existing_title):
                        para_numb = 1
                        break
                #~~~~~~Find_Pub_Time_(Optional)~~~~~~#
                if keep_pub_time == "true":
                    if pub_time_elem_locate_method == "class":
                        temp_group = target.find_elements_by_tag_name("*")
                        for temp_elem in temp_group:
                            if temp_elem.get_attribute("class") == pub_time_class:
                                pub_time_elem = temp_elem
                                if pub_time_locate_method == "text":
                                    pub_time = pub_time_elem.text
                elif keep_pub_time == "false":
                    pub_time = "None"
                #~~~~~~Insert_Into_List_Finally~~~~~~#
                if para_numb == 0:
                    basic_info = nameBasicInfo(link=link, pub_time=pub_time)
                    basic_info_list.insert(len(basic_info_list), basic_info)
                elif para_numb == 1:
                    pass
        #~~~~~~Link_Under_Target_Parent_and_Title_Under_Target~~~~~~#
        elif link_title_relation == "link_under_parent_title_under_target":
            #~~~~~~Find_Target_by_Tag~~~~~~#
            if targets_location_method == "tag":
                target_siblings = elem.find_elements_by_tag_name("*")
                for target_sibling in target_siblings:
                    if target_sibling.tag_name == targets_tag_name:
                        target = target_sibling
                        break
            elif targets_location_method == "class":
                target_siblings = elem.find_elements_by_tag_name("*")
                for target_sibling in target_siblings:
                    if target_sibling.get_attribute("class") == targets_class_name:
                        target = target_sibling
                        break
            #~~~~~~Find_Title~~~~~~#
            if title_locate_method == "text":
                title = target.text
            elif title_locate_method == "attribute": 
                title = target.get_attibute(title_attribute_name)
            #~~~~~~Find_the_Parent_Element_x_Level_above_Target~~~~~~#
            link_elem_value_str = "target"
            j = 0
            for j in (range(int(link_elem_x_path_level))):
                link_elem_value_str = link_elem_value_str + ".find_element_by_xpath(\"..\")"
            link_elem = eval(link_elem_value_str)
            #~~~~~~Such_that_We_Find_Link~~~~~~#
            link = link_elem.get_attribute(link_attribute_name)
            #~~~~~~Filter_Existing_Titles~~~~~~#
            para_numb = 0
            for existing_title in existing_title_list:
                if title.replace("\'", "“").replace("\"", "“") in str(existing_title):
                    para_numb = 1
                    break
            #~~~~~~Find_Pub_Time_(Optional)~~~~~~#
            if keep_pub_time == "true":
                if pub_time_elem_locate_method == "class":
                    temp_group = elem.find_elements_by_tag_name("*")
                    for temp_elem in temp_group:
                        if temp_elem.get_attribute("class") == pub_time_class:
                            pub_time_elem = temp_elem
                            if pub_time_locate_method == "text":
                                pub_time = pub_time_elem.text
            elif keep_pub_time == "false":
                pub_time = "None"
            #~~~~~~Insert_Into_List_Finally~~~~~~#
            if para_numb == 0:
                basic_info = nameBasicInfo(link=link, pub_time=pub_time)
                basic_info_list.insert(len(basic_info_list), basic_info)
            elif para_numb == 1:
                pass
        print(len(basic_info_list))
        if len(basic_info_list) >= int(limit):
            break
    return basic_info_list;




#~~~~~~Define_Target~~~~~~#
class nameTarget:
    def __init__(self, title, pub_time, article_text, article_input, create_user, status, bak1, flag, source, descriptor, link, bak5, bak6, bak8, img_list, source_id):
        self.title = title
        self.article_text = article_text
        self.article_input = article_input
        self.pub_time = pub_time
        self.create_user = create_user
        self.status = status
        self.bak1 = bak1
        self.flag = flag
        self.source = source
        self.descriptor = descriptor
        self.link = link
        self.bak5 = bak5
        self.bak6 = bak6
        self.bak8 = bak8
        self.img_list = img_list
        self.source_id = source_id



#~~~~~~Define_getContent~~~~~~#
from aip import AipNlp
APP_ID = '19898903'
API_KEY = 'RrPVU1NXj7NnSr532hxQAuZc'
SECRET_KEY = 'qkwhfAGp9B7pmZUiOzTPyXMm8ip2c3Dl'
url_1 = "https://aip.baidubce.com/rpc/2.0/nlp/v2/word_emb_sim"
client = AipNlp(APP_ID, API_KEY, SECRET_KEY)
def getContent(input_list, driver):
    print('getting Contents')
    content_list = []
    import time
    import datetime
    import re
    import source 
    import cv2
    import importlib 
    source = importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    import requests
    from source import table_tag
    from source import title_selector
    from source import article_parent_selector
    from source import pub_time_parent_selector
    from source import create_user
    from source import status
    from source import bak1
    from source import flag
    from source import pub_time_selector
    from source import pub_time_sibling_para
    from source import img_input_exception
    from source import img_input_exception_solution
    from source import title_exception
    from source import title_exception_solution
    from source import article_elem_start_index
    from source import article_elem_single
    from source import article_elem_locate_method
    from source import article_elem_class
    from source import article_filter
    from source import article_filter_key
    from source import img_filter
    from source import img_filter_key
    from source import source_id
    from source import sql_driver,server,database,trusted_connection,uid,pwd,port,table,table_tag,table_relation_0,table_relation_1,table_img,source,create_user,flag,status,bak1,source_id,pub_time_exception_key
    import pyodbc
    import datetime
    import dateparser

    import numpy as np
    #将新闻链接从attribute中提取出来
    for elem in input_list:
        link = elem.link
        print(link)
        pub_time = elem.pub_time
        #打开链接
        driver.get(link)
        driver.save_screenshot('current.png')
        time.sleep(2)
        #找标题
        #~~~~~~Title~~~~~~#
        try:
            title =  driver.find_element_by_css_selector(title_selector).text.replace("\'", "“").replace("\"", "“")
        except Exception as e:
            print(e)
            print("页面出错，即将跳过！")
            continue
        if title_exception == "true":
            title = eval(title_exception_solution)
        #print(link, title)#for test optional
        print_text_0 = "正在获取 <<" + title + ">> 相关信息！"
        print(print_text_0)
        #找文章源码
        #~~~~~~Article_Input~~~~~~#
        article_raw = driver.find_element_by_css_selector(article_parent_selector).find_elements_by_tag_name("*")
        article_input = ""
        img_list  = []
        i = 0
        j = 1
        text_check = ""
        img_check = ""
        for article_elem in article_raw:
            if j < int(article_elem_start_index):
                j += 1
                continue
            if article_elem_single == "true":
                if article_elem_locate_method == "class":
                    if article_elem.get_attribute("class") != article_elem_class:
                        continue
            elif article_elem_single == "false":
                pass
            elem_input = article_elem.get_attribute("outerHTML")
            elem_text = article_elem.text
            if article_filter == "true":
                if article_filter_key in elem_input:
                    continue
                elif article_filter not in elem_input:
                    pass
            elif article_filter == "false":
                pass
            if elem_text != "" and elem_text in text_check:
                continue
            text_check = text_check + elem_text
            #将图片做过滤处理
            #~~~~~~Img_Tag_Image~~~~~~#
            if article_elem.tag_name == "img":
                if img_input_exception == "true":
                    img_input_str = "article_elem.get_attribute(\"src\")" + img_input_exception_solution
                    img_input = eval(img_input_str)
                elif img_input_exception == "false":
                    img_input = article_elem.get_attribute("src")
                if img_filter == "true":
                    if img_filter_key in elem_input:
                        continue
                    else:
                        pass
                elif img_filter != "true":
                    pass
                #将图片中的二维码过滤
                #~~~~~~Filter_Out_QR_Code~~~~~~#
                file_name = "temp_img.png"
                r = requests.get(img_input, allow_redirects = True)
                with open(file_name, 'wb') as f:
                    f.write(r.content)
                    f.close()
                image = cv2.imread('temp_img.png')
                qrCodeDetector = cv2.QRCodeDetector()
                try:
                    decodedText, points, _ = qrCodeDetector.detectAndDecode(image)
                    if points is not None and decodedText.replace(" ", "") != "":
                        with open('edit_record.txt', 'a+', encoding= 'utf-8') as f:
                            print(img_input, "含有二维码， 即将跳过！")
                            f.write(img_input + "含有二维码: " + str(decodedText) + "已跳过\n")
                        continue
                    else:
                        pass
                except:
                    pass
                if img_input in img_check:
                    continue
                #将过小分辨率图片过滤
                # #~~~~~~Filter_by_Resolution~~~~~~#
                length, width = getImgSize('temp_img.png')
                if width < 80 or length < 150:
                    print(img_input, "分辨率过小，将不会在预览显示")
                    pass
                else:
                    #~~~~~~Insert~~~~~~#
                    img_list.insert(i, img_input)
                    i += 1
                img_check = img_check + img_input
            #以下为用tag方式寻找元素，相关内容同上
            #~~~~~~DIV_Tag_Image~~~~~~#
            elif article_elem.tag_name != "img" and "img" in elem_input:
                img_siblings = article_elem.find_elements_by_tag_name("*")
                for img_sibling in img_siblings:
                    if img_sibling.tag_name == "img":
                        img_elem = img_sibling
                if img_input_exception == "true":
                    img_input = eval("img_elem.get_attribute(\"src\")" + img_input_exception_solution)
                    elem_input = elem_input.replace(img_elem.get_attribute("src"), img_input)
                elif img_input_exception == "false":
                    img_input = img_elem.get_attribute("src")
                if img_filter == "true":
                    if img_filter_key in elem_input:
                        continue
                    else:
                        pass
                elif img_filter != "true":
                    pass
                #~~~~~~Filter_Out_QR_Code~~~~~~#
                file_name = "temp_img.png"
                r = requests.get(img_input, allow_redirects = True)
                with open(file_name, 'wb') as f:
                    f.write(r.content)
                    f.close()
                image = cv2.imread('temp_img.png')
                qrCodeDetector = cv2.QRCodeDetector()
                try:
                    decodedText, points, _ = qrCodeDetector.detectAndDecode(image)
                    if points is not None and decodedText.replace(" ", "") != "":
                        with open('edit_record.txt', 'a+', encoding= 'utf-8') as f:
                            print(img_input, "含有二维码， 即将跳过！")
                            f.write(img_input + "含有二维码: " + str(decodedText) + "已跳过\n")
                        continue
                    else:
                        pass
                except:
                    pass
                if img_input in img_check:
                    continue
                #~~~~~~Filter_by_Resolution~~~~~~#
                try:
                    length, width = getImgSize('temp_img.png')
                    if width < 80 or length < 150:
                        print(img_input, "分辨率过小，将不会在预览显示")
                        pass
                    else:
                        #~~~~~~Insert~~~~~~#
                        img_list.insert(i, img_input)
                        i += 1
                except:
                    pass
                img_check = img_check + img_input
            elem_input = elem_input.replace("\"", r"\"").replace("\'", r"\"")
            article_input = article_input + "\n" + elem_input
        #过滤股票i西南西
        #~~~~~~Article_text_handle~~~~~~#
        article_text = driver.find_element_by_css_selector(article_parent_selector).text
        if flag == '4':
            del_list = ['股票', "证券", "A股", "美股", "涨停"]
            occr_count = 0
            for word in del_list:
                occr = re.findall(word, article_text)
                occr_count = occr_count + len(occr)
            if occr_count >= 3:
                print("股票信息，即将跳过！")
                continue
        elif flag == "3":
            pass
        file_name = title.replace("，", "_").replace("。", "").replace("/", "_").replace("\\", "").replace("|", "_").replace("；", "_") + ".txt"
        #~~~~~~天使轮ABC特殊处理~~~~~~#
        if '天使轮' in article_text:
            bak11 = '171'
            add_word = '天使轮'
        if 'A轮' in article_text:
            bak11 = '171'
            add_word = 'A轮'
        if 'B轮' in article_text:
            bak11 = '171'
            add_word = 'B轮'
        if 'C轮' in article_text:
            bak11 = '171'
            add_word = 'C轮'
        #找新闻发布时间
        #~~~~~~Pub_Time~~~~~~#
        try:
            pub_time = driver.find_element_by_css_selector(pub_time_selector).text
        except:
            print("页面出错！即将跳过...")
            continue
        pub_time_raw = pub_time
        pub_time = str(dateparser.parse(pub_time.replace(pub_time_exception_key, '').replace(' ', '')))
        print(pub_time_raw, pub_time)
       #找新闻标签
       #~~~~~~Descriptors~~~~~~#
        try: 
            output = client.newsSummary(article_text.encode('gbk', 'ignore').decode('gbk', 'ignore'), 300)
        except: pass
        try: descriptor = output['summary']
        except Exception as e:
            j = 0
            article_half = ""
            for elem in article_text:
                article_half = article_half + elem
                j += 1
                if j > 1000:
                    break
            try:
                output = client.newsSummary(article_half.encode('gbk', 'ignore').decode('gbk', 'ignore'), 300)
                descriptor = output['summary']
            except:
                descriptor = " "
        #~~~~~~bak5_and_bak6~~~~~~#
        #链接数据库查找关联词所关联的主词作为标签
        #~~~~~~Connect_to_Sql~~~~~~#
        try:
            connect_input = "driver=" + sql_driver + ";"
            connect_input = connect_input + "Server=" + server + ";"
            connect_input = connect_input + "Database=" + database + ";"
            connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
            if port != "":
                connect_input = connect_input + "PORT=" + port + ";"
            if uid != '' and pwd != '':
                connect_input = connect_input + "UID=" + uid + ";"
                connect_input = connect_input + "PWD=" + pwd
            conn = pyodbc.connect(connect_input)
            cursor = conn.cursor()
        except Exception as e:
            print(e)
            pass
        order_2 = 'select id,Name,RelateTag from ' + table_tag
        cursor.execute(order_2)
        tag_pair_list = list(cursor.fetchall())
        bak5 = ""
        bak6 = ""
        for tag_pair in tag_pair_list:
            relate_word_list = str(tag_pair[2]).split('$')
            para_numb = 0
            for relate_word in relate_word_list:
                if relate_word in article_text:
                    if relate_word == "":
                        continue
                    para_numb = 1
                    break
            if para_numb == 1:
                if bak5 != "":
                    bak5 = bak5 + "," + str(tag_pair[0])
                    bak6 = bak6 + "$" + tag_pair[1]
                elif bak5 == "":
                    bak5 = bak5 + str(tag_pair[0])
                    bak6 = bak6 + tag_pair[1]
        if bak5 == "" and flag == "4":
            print("无相关标签，即将跳过！")
            continue
        elif bak5 == "" and flag == "3":
            bak5 = "17"
            bak6 = "互联网"
         #~~~~~~bak8_Part~~~~~~#
        try:
            output = client.keyword(title.encode('gbk', 'ignore').decode('gbk', 'ignore'), article_text.encode('gbk', 'ignore').decode('gbk', 'ignore'))
            items = list(output['items'])
            keywords_input = ""
            keyword_list = []
            for item in items: 
                keyword = dict(item)['tag']
                keyword_list.insert(len(keyword_list), keyword)
            keywords_input = str(keyword_list).replace("[\'", "").replace("\']", "").replace("\', \'", "$")
            bak8 = bak6 + "$" + keywords_input
        except:
            bak8 = bak6
        #~~~~~~天使轮特殊处理~~~~~#
        if bak1 == 171:
            bak8 = bak8 + "$" + add_word
            bak6 = bak6 + "$" + add_word
        #将每篇文章命名于nameTarget的class，以便于插入数据库
        #~~~~~~Arrange_into_Target_Frame~~~~~~#
        target = nameTarget(title=title, pub_time=pub_time, article_text=article_text, article_input=article_input, create_user=create_user, status=status, bak1=bak1, flag=flag, source=source, descriptor=descriptor, link=link, bak5=bak5, bak6=bak6, bak8=bak8, img_list=img_list, source_id=source_id)
        content_list.insert(len(content_list), target)
        #break #for test optional
    return content_list;


#将数据插入数据库
#~~~~~~Define_insertSql~~~~~~#
def insertSql(input_list):
    import datetime
    import pyodbc
    import source
    import importlib 
    source = importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    from source import sql_driver,server,database,trusted_connection,uid,pwd,port,table,table_tag,table_relation_0,table_relation_1,table_img,source,create_user,flag,status,bak1,source_id
    output_list = []
    i = 0
    #对每篇for loop中的元素进行文本上的整理，做成该文章插入数据库的命令
    for elem in input_list:
        #将每个attr变为变量
        #~~~~~~Define_Variables~~~~~~#
        title = elem.title
        pub_time = elem.pub_time
        article_input = elem.article_input
        create_user = elem.create_user
        status = elem.status
        bak1 = elem.bak1
        flag = elem.flag
        source = elem.source
        descriptor = elem.descriptor
        link = elem.link
        current_time = datetime.datetime.now()
        current_time = str(current_time.strftime("%Y-%m-%d %H:%M"))
        print('current_time', current_time)
        bak5 = elem.bak5 
        bak6 = elem.bak6 
        bak8 = elem.bak8 
        source_id = elem.source_id
        #连接数据库并插入数据
        #~~~~~~Connect_to_SQL~~~~~~#
        connect_input = "driver=" + sql_driver + ";"
        connect_input = connect_input + "Server=" + server + ";"
        connect_input = connect_input + "Database=" + database + ";"
        connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
        if port != "":
            connect_input = connect_input + "PORT=" + port + ";"
        if uid != '' and pwd != '':
            connect_input = connect_input + "UID=" + uid + ";"
            connect_input = connect_input + "PWD=" + pwd
        conn = pyodbc.connect(connect_input)
        cursor = conn.cursor()
        order_9 = "INSERT INTO " + table + "(ID, PubTime, Title, Contents, CreateDate, CreateUser, Flag, Source, bak1, Status, Brief, LinkUrl, bak5, bak6, bak8, SourceID) VALUES (newid(), \'" + pub_time + "', '" + title + "', N'" + article_input + "', '" + current_time + "', '" + create_user + "', '" + flag + "', '" + source + "', '" + bak1 + "', '" + status + "', '" + descriptor + "', '" + link + "', '" + bak5 + "', '" + bak6 + "', '" + bak8 + "', '" + source_id + "')"
        try: 
            cursor.execute(order_9)
        #如果有特殊语法错误，可能会导致无法成功插入数据库，此时将会覆盖之前的exception.txt
        #并且在exception.txt上更新新的未执行成功的数据库指令，每次代码未成功爬取文章时可查询
        #exception.txt，在query里执行该string，以测试bug
        except Exception as e:
            with open('exception.txt', 'w+', encoding='utf-8') as f:
                f.write(order_9)
                f.close()
            continue
        conn.commit()
        print_text_1 = "已将《" + title + "》导入 SQL!"
        order_8 = 'select id from ' + table + ' where Title = \'' + title + '\''
        cursor.execute(order_8)
        elem_id = cursor.fetchone()[0]
        output_list.insert(i, elem_id)
        i += 1
        print(print_text_1)
    return output_list 




#关联企业
#~~~~~~Insert_Enterprise_Relation~~~~~~#
def enterpriseRelation(input_list):
    import pyodbc
    import source 
    import importlib 
    source = importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    from source import table_relation_1
    from source import table_relation_0
    from source import sql_driver,server,database,trusted_connection,uid,pwd,port,table,table_tag,table_relation_0,table_relation_1,table_img
    #链接数据库
    #~~~~~~Connect_to_SQL~~~~~~#
    connect_input = "driver=" + sql_driver + ";"
    connect_input = connect_input + "Server=" + server + ";"
    connect_input = connect_input + "Database=" + database + ";"
    connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
    if port != "":
        connect_input = connect_input + "PORT=" + port + ";"
    if uid != '' and pwd != '':
        connect_input = connect_input + "UID=" + uid + ";"
        connect_input = connect_input + "PWD=" + pwd
    conn = pyodbc.connect(connect_input)
    cursor = conn.cursor()
    #将所有企业关联词收集到list里
    #~~~~~Get_Related_Section_Parameters_Ready~~~~~~#
    enterprise_relate_list = []
    order_5 = 'select Enterprisename,Description,ID from ' + table_relation_1 + ' where Flag != \'3\''
    cursor.execute(order_5)
    enterprise_relate_list = list(cursor.fetchall())
    #将企业关联词和文章文本进行对比以找出关联企业
    i = 0
    for elem in input_list:
        title = elem.title
        article_text = elem.article_text
        order_3 = 'select id from ' + table + ' where Title = \'' + title + '\''
        cursor.execute(order_3)
        try:news_id = cursor.fetchall()[0][0]
        except: continue
        print(title, news_id)
        contained_enterprise_id_list = []
        contained_enterprise_name_list = []
        j = 0
        k = 0
        for enterprise_relate in enterprise_relate_list:
            description = enterprise_relate[1]
            if description in article_text:
                contained_enterprise_id_list.insert(j, enterprise_relate[2])
                contained_enterprise_name_list.insert(j, enterprise_relate[0])
                j += 1
            k += 1
        j = 0
        for contained_enterprise_id, contained_enterprise_name in zip(contained_enterprise_id_list,contained_enterprise_name_list):
            order_6 = "INSERT INTO " + table_relation_0 + "(NewsID, EnterpriseName, EnterpriseID) VALUES (\'" + str(news_id) + "', '" + str(contained_enterprise_name_list[j]) + "', '" + str(contained_enterprise_id_list[j]) + "')"
            cursor.execute(order_6)
            conn.commit()
            j += 1



#退出浏览器
#~~~~~~Define_quitFirefox~~~~~~#
def quitFirefox(driver):
    driver.quit()
    import psutil
    import datetime
    import os

    processes = psutil.process_iter()
    for process in processes:
        try: name = process.name()
        except: continue
        if process.name() == "firefox.exe":
            try:
                process.kill()
            except Exception as exception:
                with open ('end_task.txt', 'a+', encoding='utf-8') as f:
                    f.write(process.name() + str(process.pid) + "can't be closed at " + str(datetime.datetime.now()) + "due to" + exception + "\n")
                    f.close()
                    continue
            with open ('end_task.txt', 'a+', encoding='utf-8') as f:
                f.write(process.name() + " " + str(process.pid) + " closed at " + str(datetime.datetime.now()) + "\n")
                f.close()
        elif process.name() == "geckodriver.exe":
            try:
                process.kill()
            except Exception as exception:
                with open ('end_task.txt', 'a+', encoding='utf-8') as f:
                    f.write(process.name() + str(process.pid) + "can't be closed at " + str(datetime.datetime.now()) + "due to" + exception + "\n")
                    f.close()
                    continue
            with open ('end_task.txt', 'a+', encoding='utf-8') as f:
                f.writelines(process.name() + " " + str(process.pid) + " closed at " + str(datetime.datetime.now()) +"\n")



#将图片插入img的表格中以便于在前端手机端文章预览界面中显示
#~~~~~~insertImages~~~~~~#
def insertImages(content_list, sql_id_list):
    import pyodbc
    from source import table_img
    import source
    import importlib 
    source = importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    from source import sql_driver,server,database,trusted_connection,uid,pwd,port,table,table_tag,table_relation_0,table_relation_1,table_img
    #连接数据库
    connect_input = "driver=" + sql_driver + ";"
    connect_input = connect_input + "Server=" + server + ";"
    connect_input = connect_input + "Database=" + database + ";"
    connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
    if port != "":
        connect_input = connect_input + "PORT=" + port + ";"
    if uid != '' and pwd != '':
        connect_input = connect_input + "UID=" + uid + ";"
        connect_input = connect_input + "PWD=" + pwd
    conn = pyodbc.connect(connect_input)
    cursor = conn.cursor()
    for elem_content,elem_id in zip(content_list, sql_id_list):
        img_list = elem_content.img_list
        j = 1#j为该片文章中的图片序号，显示顺序按序号排
        if img_list == []:
            continue
        for img in img_list:
            order_7 = "INSERT INTO " + table_img + "(NewsID, Url, [Order]) VALUES" + "('" + str(elem_id) + "', N'" + img + "', '" + str(j) + "')"
            j += 1
            cursor.execute(order_7)
            conn.commit()


def checkImg():
    import pyodbc
    import datetime
    import sys
    import cv2
    import requests
    import os
    import source
    import importlib 
    source = importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    from source import sql_driver,server,database,trusted_connection,uid,pwd,port,table,table_tag,table_relation_0,table_relation_1,table_img,source,create_user,flag,status,bak1,source_id
    #连接数据库
    #~~~~~~Connect_to_SQL~~~~~~#
    connect_input = "driver=" + sql_driver + ";"
    connect_input = connect_input + "Server=" + server + ";"
    connect_input = connect_input + "Database=" + database + ";"
    connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
    if port != "":
        connect_input = connect_input + "PORT=" + port + ";"
    if uid != '' and pwd != '':
        connect_input = connect_input + "UID=" + uid + ";"
        connect_input = connect_input + "PWD=" + pwd
    conn = pyodbc.connect(connect_input)
    cursor = conn.cursor()
    #~~~~~Delete_Repeat_Pictures~~~~~~#
    start_time = datetime.datetime.now()
    time_delta = datetime.timedelta(hours=3)
    start_time = start_time - time_delta
    start_time = start_time.strftime("%Y-%m-%d %H:%M:%S")
    start_time = str(start_time)
    order_0 = "select ID, Title from " + table + " where CreateDate > \'" + start_time + "\' and (Flag = \'3\' or Flag = \'4\')"
    cursor.execute(order_0)
    item_list = list(cursor.fetchall())
    #将所有文章关联的图片找出
    for item in item_list:
        news_id = item[0]
        title = item[1]
        order_1 = "select Url from " + table_img + " where NewsID =\'" + news_id + "\'"
        cursor.execute(order_1)
        urls_raw = list(cursor.fetchall())
        url_list = []
        for url_raw in urls_raw:
            url = url_raw[0]
            url_list.insert(len(url_list), url)
        #将所有的url都导入到一个set当中
        url_list = list(set(url_list))
        order_3 = "delete " + table_img + " where NewsID = \'" + news_id + "\'"
        cursor.execute(order_3)
        cursor.commit()
        i = 1
        #删除所有预览图片，插入set中的每个图片以去除重复图片
        for url in url_list:
            order_4 = " insert into " + table_img +  " (NewsID, Url, [Order]) values (\'" + news_id + "', N'" + url + "', '" + str(i) + "')"
            cursor.execute(order_4)
            cursor.commit()
            i += 1


#将新闻乱序
def randomizeNews():
    import pyodbc
    import datetime
    from random import choices
    import source
    import importlib 
    source = importlib.__import__('source')
    importlib.reload(importlib.__import__('source'))
    from source import sql_driver,server,database,trusted_connection,uid,pwd,port,table,table_tag,table_relation_0,table_relation_1,table_img,source,create_user,flag,status,bak1,source_id
    #连接数据库
    #~~~~~~Connect_to_SQL~~~~~~#
    connect_input = "driver=" + sql_driver + ";"
    connect_input = connect_input + "Server=" + server + ";"
    connect_input = connect_input + "Database=" + database + ";"
    connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
    if port != "":
        connect_input = connect_input + "PORT=" + port + ";"
    if uid != '' and pwd != '':
        connect_input = connect_input + "UID=" + uid + ";"
        connect_input = connect_input + "PWD=" + pwd
    conn = pyodbc.connect(connect_input)
    cursor = conn.cursor()
    print("数据库已连接！")
    #选中过去三小时内的文章
    #~~~~~~Select_Articles_within_Current_Hour~~~~~~#
    current_time = datetime.datetime.now()
    year = current_time.year
    month = current_time.month
    day = current_time.day
    hour = current_time.hour
    start_time = datetime.datetime(year=year, month=month, day=day, hour=hour-3, minute=0)
    end_time = datetime.datetime(year=year, month=month, day=day, hour=hour+1, minute=0)
    order_0 = "select ID, Source, Title from " + table + " where CreateDate > \'" + str(start_time) + "\' and CreateDate < \'" + str(end_time) + "\'"
    ##############sourceID_choice = random.choice(sourceID_list)
    cursor.execute(order_0)
    raw_list = cursor.fetchall()
    source_list = []
    #将所有的source放到source_list里
    for elem in raw_list:
        source = elem[1]
        source_list.insert(len(source_list), source)
    raw_source_list = source_list
    #统计所有的source种类
    source_list = list(set(source_list))
    source_total = len(source_list)
    source_numb_list = list(range(0, source_total))
    print("正在整理", str(len(raw_list)), "条新闻顺序. 共", str(len(source_list)), "类新闻来源.")

    #寻找每个source的权重p_n
    a = 0
    for elem in source_list:
        p_string = "p_" + str(a)
        b = 0
        for elem_raw in raw_source_list:
            if elem_raw == elem:
                b = b + 1
        vars()[p_string] = b
        a += 1

    #寻找加权平均数分母bass_numb
    base_numb = 0
    for i in range(0, len(source_list), 1):
        p_string = "p_" + str(i)
        base_numb = base_numb + vars()[p_string]

    #计算对于每个source的文章在所有文章中的比例
    #用此概率选择文章可使乱序效果最随机化
    pr_list = []
    for i in range(0, len(source_list), 1):
        p_string = "p_" + str(i)
        pr_string = "pr_" + str(i)
        vars()[pr_string] = vars()[p_string]/base_numb
        pr_list.insert(len(pr_list), vars()[pr_string])

    print(source_list)
    with open('randomizeNews_Journal.txt', 'a+', encoding='utf-8') as f:
        f.write(str(current_time) + '\n' + str(source_list) + '\n\n')
    print(pr_list)

        
    #将乱序所须变量及所print变量定义成一个class
    class nameSet:
        def __init__(self, the_id, source, check_numb, title, img_numb):
            self.the_id = the_id
            self.source = source
            self.check_numb = check_numb
            self.title = title
            self.img_numb = img_numb

    raw_list_0 = []
    raw_list_1 = []
    #将所有没图的放在手机端显示的后面，bak11较小的数字，先进行乱序
    #用概率选择每篇待选文章所属的source，在该source里随机抽取文章
    for elem in raw_list:
        the_id = elem[0]
        source = elem[1]
        title = elem[2]
        check_numb = 0
        order_1 = 'select Url from ' + table_img + ' where NewsID = \'' + the_id + "\'"
        cursor.execute(order_1)
        img_list = list(cursor.fetchall())
        img_numb = len(img_list)
        if img_numb != 0:
            print(img_list, img_numb)
        if img_numb == 0:
            the_set = nameSet(the_id=the_id, source=source, check_numb=check_numb, title=title, img_numb=img_numb)
            raw_list_0.insert(len(raw_list_0), the_set)
        elif img_numb > 0:
            the_set = nameSet(the_id=the_id, source=source, check_numb=check_numb, title=title, img_numb=img_numb)
            raw_list_1.insert(len(raw_list_1), the_set)
    ######################################################
    print(len(raw_list_0), len(raw_list_1))
    j = 1
    final_list = []
    for i in range(1, len(raw_list_0)+1, 1):
        para_numb = 0
        while True:
            source_numb_choice = choices(source_numb_list, pr_list)[0]
            source_choice = source_list[source_numb_choice]
            k = 0
            for elem in raw_list_0:
                check_numb = elem.check_numb
                if check_numb == 1:
                    k += 1
                    continue
                source = elem.source
                the_id = elem.the_id
                title = elem.title
                img_numb = elem.img_numb
                if source == source_choice:
                    para_numb = 1
                    raw_list_0[k].check_numb = 1
                    break
                k += 1
            if para_numb == 1:
                break
            elif para_numb == 0:
                continue
        pair_list = [the_id, str(j), source, title, img_numb]
        j += 1
        final_list.insert(len(final_list), pair_list)

    order_1 = "select Max(bak11) from " + table + " where CreateDate < \'" + str(start_time) + "\'"
    cursor.execute(order_1)
    max_numb = cursor.fetchall()[0][0]
    if max_numb == None:
        max_numb = 0
    elif max_numb != None:
        max_numb = int(max_numb)
    for elem in final_list:
        the_id = elem[0]
        bak11 = int(elem[1]) + max_numb
        print(the_id, elem[3], elem[2], bak11)
        order_2 = "update " + table + " set bak11=\'" + str(bak11) + "\' where ID=\'" + the_id + "\'"
        cursor.execute(order_2)
        cursor.commit()
    ####################################################################
    j = 1
    final_list = []
    for i in range(1, len(raw_list_1)+1, 1):
        para_numb = 0
        while True:
            source_numb_choice = choices(source_numb_list, pr_list)[0]
            source_choice = source_list[source_numb_choice]
            k = 0
            for elem in raw_list_1:
                check_numb = elem.check_numb
                if check_numb == 1:
                    k += 1
                    continue
                source = elem.source
                the_id = elem.the_id
                title = elem.title
                img_numb = elem.img_numb
                if source == source_choice:
                    para_numb = 1
                    raw_list_1[k].check_numb = 1
                    break
                k += 1
            if para_numb == 1:
                break
            elif para_numb == 0:
                continue
        pair_list = [the_id, str(j), source, title, img_numb]
        j += 1
        final_list.insert(len(final_list), pair_list)

    order_1 = "select Max(bak11) from " + table + " where CreateDate < \'" + str(start_time) + "\'"
    cursor.execute(order_1)
    max_numb = cursor.fetchall()[0][0]
    if max_numb == None:
        max_numb = 0
    elif max_numb != None:
        max_numb = int(max_numb)
    for elem in final_list:
        the_id = elem[0]
        bak11 = int(elem[1]) + max_numb
        print(the_id, elem[3], elem[2], bak11)
        order_2 = "update " + table + " set bak11=\'" + str(bak11) + "\' where ID=\'" + the_id + "\'"
        cursor.execute(order_2)
        cursor.commit()



def getImgSize(fname):
    import struct
    import imghdr
    '''Determine the image type of fhandle and return its size.
    from draco'''
    with open(fname, 'rb') as fhandle:
        head = fhandle.read(24)
        if len(head) != 24:
            return
        if imghdr.what(fname) == 'png':
            check = struct.unpack('>i', head[4:8])[0]
            if check != 0x0d0a1a0a:
                return
            width, height = struct.unpack('>ii', head[16:24])
        elif imghdr.what(fname) == 'gif':
            width, height = struct.unpack('<HH', head[6:10])
        elif imghdr.what(fname) == 'jpeg':
            try:
                fhandle.seek(0) # Read 0xff next
                size = 2
                ftype = 0
                while not 0xc0 <= ftype <= 0xcf:
                    fhandle.seek(size, 1)
                    byte = fhandle.read(1)
                    while ord(byte) == 0xff:
                        byte = fhandle.read(1)
                    ftype = ord(byte)
                    size = struct.unpack('>H', fhandle.read(2))[0] - 2
                # We are at a SOFn block
                fhandle.seek(1, 1)  # Skip `precision' byte.
                height, width = struct.unpack('>HH', fhandle.read(4))
            except Exception: #IGNORE:W0703
                return
        else:
            return
        return width, height


